/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package east2west.dao;

import east2west.dto.FlightsDTO;
import east2west.interfaces.FlightsInterface;
import east2west.util.DatabaseUtil;
import east2west.util.DateTimeUtil;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/**
 *
 * @author nguyendat
 */
public class FlightDAO implements FlightsInterface {

    Connection con = null;
    CallableStatement call = null;
    ResultSet rs = null;
    Statement st = null;

    public ArrayList<FlightsDTO> readByAll() {
        ArrayList<FlightsDTO> list = null;
        FlightsDTO dto = null;
        try {
            con = DatabaseUtil.getConnection();
            st = con.createStatement();
            rs = st.executeQuery("select * from V_Flights");
            list = new ArrayList<FlightsDTO>();
            while (rs.next()) {
                dto = new FlightsDTO();
                dto.setFlightId(rs.getInt(1));
                dto.setFlightCode(rs.getString(2));
                dto.setDeparture(rs.getString(3));
                dto.setDestination(rs.getString(4));
                Date date = rs.getDate(5);
                if (date == null) {
                    dto.setFlightDate("");
                } else {
                    dto.setFlightDate(DateTimeUtil.convert2String(date, "dd/MM/yyyy"));
                }
                dto.setFlightTime(rs.getString(6));
                dto.setWebSite(rs.getString(7));
                dto.setEmail(rs.getString(8));
                dto.setDescription(rs.getString(9));
                list.add(dto);
            }
            rs.close();
            st.close();
            return list;
        } catch (Exception ex) {

            return null;
        }
    }

    public FlightsDTO findById(int id) {
        FlightsDTO dto = null;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("SELECT * FROM V_Flights WHERE FlightId = ?");
            call.setInt(1, id);
            rs = call.executeQuery();
            while (rs.next()) {
                dto = new FlightsDTO();
                dto.setFlightId(rs.getInt(1));
                dto.setFlightCode(rs.getString(2));
                dto.setDeparture(rs.getString(3));
                dto.setDestination(rs.getString(4));
                Date date = rs.getDate(5);
                if (date == null) {
                    dto.setFlightDate("");
                } else {
                    dto.setFlightDate(DateTimeUtil.convert2String(date, "dd/MM/yyyy"));
                }
                dto.setFlightTime(rs.getString(6));
                dto.setWebSite(rs.getString(7));
                dto.setEmail(rs.getString(8));
                dto.setDescription(rs.getString(9));
            }
            rs.close();
            call.close();
            return dto;
        } catch (SQLException ex) {

            return null;
        }


    }

    public ArrayList<FlightsDTO> findByCode(String code) {
        FlightsDTO dto = null;
        ArrayList<FlightsDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("{call sp_Call_V_Flight_FindCode(?)}");
            call.setString(1, code);
            rs = call.executeQuery();
            while (rs.next()) {
                dto = new FlightsDTO();
                dto.setFlightId(rs.getInt(1));
                dto.setFlightCode(rs.getString(2));
                dto.setDeparture(rs.getString(3));
                dto.setDestination(rs.getString(4));
                dto.setFlightDate(rs.getString(5));
                dto.setFlightTime(rs.getString(6));
                dto.setWebSite(rs.getString(7));
                dto.setEmail(rs.getString(8));
                dto.setDescription(rs.getString(9));
                list.add(dto);
            }
            rs.close();
            call.close();
            return list;
        } catch (SQLException ex) {

            return null;
        }


    }

    public ArrayList<FlightsDTO> findByDeparture(String departure) {
        FlightsDTO dto = null;
        ArrayList<FlightsDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("{call sp_Call_V_Flight_FindDeparture(?)}");
            call.setString(1, departure);
            ResultSet rs = call.executeQuery();
            while (rs.next()) {
                dto = new FlightsDTO();
                dto.setFlightId(rs.getInt(1));
                dto.setFlightCode(rs.getString(2));

                dto.setDeparture(rs.getString(3));
                dto.setDestination(rs.getString(4));
                dto.setFlightDate(rs.getString(5));
                dto.setFlightTime(rs.getString(6));
                dto.setWebSite(rs.getString(7));
                dto.setEmail(rs.getString(8));
                dto.setDescription(rs.getString(9));
                list.add(dto);
            }
            rs.close();
            call.close();
            return list;
        } catch (SQLException ex) {

            return null;
        }



    }

    public ArrayList<FlightsDTO> findByDestination(String destination) {
        FlightsDTO dto = null;
        ArrayList<FlightsDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("{call sp_Call_V_Flight_FindDestination(?)}");
            call.setString(1, destination);
            ResultSet rs = call.executeQuery();
            while (rs.next()) {
                dto = new FlightsDTO();
                dto.setFlightId(rs.getInt(1));
                dto.setFlightCode(rs.getString(2));

                dto.setDeparture(rs.getString(3));
                dto.setDestination(rs.getString(4));
                dto.setFlightDate(rs.getString(5));
                dto.setFlightTime(rs.getString(6));
                dto.setWebSite(rs.getString(7));
                dto.setEmail(rs.getString(8));
                dto.setDescription(rs.getString(9));
                list.add(dto);
            }
            rs.close();
            call.close();
            return list;
        } catch (SQLException ex) {


            return null;
        }


    }

    public ArrayList<FlightsDTO> findByFlightDate(String date) {
        FlightsDTO dto = null;
        ArrayList<FlightsDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("{call sp_Call_V_Flight_FindDate(?)}");
            call.setString(1, date);
            ResultSet rs = call.executeQuery();
            while (rs.next()) {
                dto = new FlightsDTO();
                dto.setFlightId(rs.getInt(1));
                dto.setFlightCode(rs.getString(2));

                dto.setDeparture(rs.getString(3));
                dto.setDestination(rs.getString(4));
                dto.setFlightDate(rs.getString(5));
                dto.setFlightTime(rs.getString(6));
                dto.setWebSite(rs.getString(7));
                dto.setEmail(rs.getString(8));
                dto.setDescription(rs.getString(9));
                list.add(dto);
            }
            rs.close();
            call.close();
            return list;
        } catch (SQLException ex) {


            return null;
        }

    }

    public ArrayList<FlightsDTO> findByFlightTime(String time) {
        FlightsDTO dto = null;
        ArrayList<FlightsDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("{call sp_Call_V_Flight_FindTime(?)}");
            call.setString(1, time);
            rs = call.executeQuery();
            while (rs.next()) {
                dto = new FlightsDTO();
                dto.setFlightId(rs.getInt(1));
                dto.setFlightCode(rs.getString(2));

                dto.setDeparture(rs.getString(3));
                dto.setDestination(rs.getString(4));
                dto.setFlightDate(rs.getString(5));
                dto.setFlightTime(rs.getString(6));
                dto.setWebSite(rs.getString(7));
                dto.setEmail(rs.getString(8));
                dto.setDescription(rs.getString(9));
                list.add(dto);
            }

            return list;
        } catch (SQLException ex) {


            return null;
        }

    }

    public ArrayList<FlightsDTO> findByDTO(FlightsDTO dto) {


        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("{call sp_Search_Flights(?,?)}");
            call.setString(1, dto.getDeparture());
            call.setString(2, dto.getDestination());
            rs = call.executeQuery();
            ArrayList<FlightsDTO> listSearch = new ArrayList<FlightsDTO>();
            while (rs.next()) {
                FlightsDTO flight = new FlightsDTO();
                flight.setFlightId(rs.getInt(1));
                flight.setFlightCode(rs.getString(2));
                flight.setDeparture(rs.getString(3));
                flight.setDestination(rs.getString(4));
                Date date = rs.getDate(5);
                if (date == null) {
                    flight.setFlightDate("");
                } else {
                    flight.setFlightDate(DateTimeUtil.convert2String(date, "dd/MM/yyyy"));
                }
                flight.setFlightTime(rs.getString(6));
                flight.setWebSite(rs.getString(7));
                flight.setEmail(rs.getString(8));
                flight.setDescription(rs.getString(9));

                listSearch.add(flight);
            }

            return listSearch;
        } catch (SQLException ex) {


            return null;
        }
    }

    public boolean create(FlightsDTO flight) {
        try {
            con = DatabaseUtil.getConnection();

            call = con.prepareCall("{call sp_Insert_Flights(?,?,?,?,?,?,?,?)}");

            call.setString(1, flight.getFlightCode());
            call.setString(2, flight.getDeparture());
            call.setString(3, flight.getDestination());
            call.setString(4, flight.getFlightDate());
            call.setString(5, flight.getFlightTime());
            call.setString(6, flight.getWebSite());
            call.setString(7, flight.getEmail());
            call.setString(8, flight.getDescription());
            call.executeUpdate();

            return true;


        } catch (SQLException ex) {
            return false;
        } finally {
            try {
                if (con != null && !con.isClosed()) {
                    DatabaseUtil.closeConnection(con);
                }
            } catch (SQLException ex) {
            }
        }
    }

    public boolean update(FlightsDTO flight) {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("{call sp_Update_Flights(?,?,?,?,?,?,?,?,?)}");
            call.setInt(1, flight.getFlightId());
            call.setString(2, flight.getFlightCode());
            call.setString(3, flight.getDeparture());
            call.setString(4, flight.getDestination());
            call.setString(5, flight.getFlightDate());
            call.setString(6, flight.getFlightTime());
            call.setString(7, flight.getWebSite());
            call.setString(8, flight.getEmail());
            call.setString(9, flight.getDescription());
            call.executeUpdate();
            call.close();
            return true;
        } catch (SQLException ex) {
            return false;
        } finally {
            try {
                if (con != null && !con.isClosed()) {
                    DatabaseUtil.closeConnection(con);
                }
            } catch (SQLException ex) {
            }
        }
    }

    public boolean delete(FlightsDTO flight) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public boolean delete(int id) {
        try {
            con = DatabaseUtil.getConnection();

            call = con.prepareCall("{call sp_Delete_Flights(?)}");
            call.setInt(1, id);
            call.executeUpdate();
            call.close();
            return true;
        } catch (SQLException ex) {
            return false;
        } finally {
            try {
                if (con != null && !con.isClosed()) {
                    DatabaseUtil.closeConnection(con);
                }
            } catch (SQLException ex) {
            }
        }
    }

    public ArrayList<FlightsDTO> findByTop() {
        ArrayList<FlightsDTO> list = null;
        FlightsDTO dto = null;
        try {
            con = DatabaseUtil.getConnection();
            st = con.createStatement();
            rs = st.executeQuery("select top 4 * from V_Flights");
            list = new ArrayList<FlightsDTO>();
            while (rs.next()) {
                dto = new FlightsDTO();
                dto.setFlightId(rs.getInt(1));
                dto.setFlightCode(rs.getString(2));
                dto.setDeparture(rs.getString(3));
                dto.setDestination(rs.getString(4));
                Date date = rs.getDate(5);
                if (date == null) {
                    dto.setFlightDate("");
                } else {
                    dto.setFlightDate(DateTimeUtil.convert2String(date, "dd/MM/yyyy"));
                }
                dto.setFlightTime(rs.getString(6));
                dto.setWebSite(rs.getString(7));
                dto.setEmail(rs.getString(8));
                dto.setDescription(rs.getString(9));
                list.add(dto);
            }
            rs.close();
            st.close();
            return list;
        } catch (Exception ex) {

            return null;
        }
    }

    public ArrayList<FlightsDTO> findAllOrtherId(int id) {

        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("SELECT * FROM V_Flights WHERE FlightId != ?");
            call.setInt(1, id);
            rs = call.executeQuery();
            ArrayList<FlightsDTO> list = new ArrayList<FlightsDTO>();
            while (rs.next()) {
                FlightsDTO dto = new FlightsDTO();
                dto.setFlightId(rs.getInt(1));
                dto.setFlightCode(rs.getString(2));
                dto.setDeparture(rs.getString(3));
                dto.setDestination(rs.getString(4));
                Date date = rs.getDate(5);
                if (date == null) {
                    dto.setFlightDate("");
                } else {
                    dto.setFlightDate(DateTimeUtil.convert2String(date, "dd/MM/yyyy"));
                }
                dto.setFlightTime(rs.getString(6));
                dto.setWebSite(rs.getString(7));
                dto.setEmail(rs.getString(8));
                dto.setDescription(rs.getString(9));
                list.add(dto);
            }

            return list;
        } catch (SQLException ex) {

            return null;
        }
    }

    public ArrayList<FlightsDTO> getFlightDeparture() {
        ArrayList<FlightsDTO> list = null;
        FlightsDTO dto = null;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("select Distinct Departure from V_Flights");
            rs = call.executeQuery();
            list = new ArrayList<FlightsDTO>();
            while (rs.next()) {
                dto = new FlightsDTO();
                dto.setDeparture(rs.getString(1));
                list.add(dto);
            }

            return list;
        } catch (Exception ex) {

            return null;
        }

    }

    public ArrayList<FlightsDTO> getFlightDestination() {
        ArrayList<FlightsDTO> list = null;
        FlightsDTO dto = null;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("select Distinct Destination from V_Flights");
            rs = call.executeQuery();
            list = new ArrayList<FlightsDTO>();
            while (rs.next()) {
                dto = new FlightsDTO();
                dto.setDestination(rs.getString(1));
                list.add(dto);
            }

            return list;
        } catch (Exception ex) {

            return null;
        }

    }
}
